package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.maszy.bean.Member;
import com.maszy.db.DBConnection;

public class MemberService {
	Member service = new Member();
	private static PreparedStatement pst;
	private static ResultSet rs = null;

	public int add(Member obj) {
		DBConnection con = new DBConnection();
		int result = 0;
		String sql = "insert into Maszy_Member(Maszy_Member_Id,Maszy_Member_Name,Maszy_Member_Phone,Maszy_Member_Adress,Maszy_MemberCard_Id,Maszy_Member_Remark) values(?,?,?,?,?,?)";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pst.setLong(1, obj.getMember_Id());
			pst.setString(2, obj.getMember_Name());
			pst.setString(3, obj.getMember_Phone());
			pst.setString(4, obj.getMember_Adress());
			pst.setLong(5,obj.getMemberCard_Id());
			pst.setString(6, obj.getMember_Remark());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return result;
	}

	public int update(Member obj) {
		DBConnection con = new DBConnection();
		int i = 0;
		String sql = "update Maszy_Member set Maszy_Member_Name=?,Maszy_Member_Phone=?,Maszy_Member_Adress=?,Maszy_MemberCard_Id=?,Maszy_Member_Remark=? where Maszy_Member_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			pst.setString(1, obj.getMember_Name());
			pst.setString(2, obj.getMember_Phone());
			pst.setString(3, obj.getMember_Adress());
			pst.setLong(4, obj.getMemberCard_Id());
			pst.setString(5, obj.getMember_Remark());
			pst.setLong(6, obj.getMember_Id());
			i = pst.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}

	public int delete(Member obj) {
		DBConnection con = new DBConnection();
		int i = 0;
		String sql = "delete from  Maszy_Member  where Maszy_Member_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setLong(1, obj.getMember_Id());
			i = pst.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}

	public Member queryById(Member obj) {
		DBConnection con = new DBConnection();
		String sql = "select * from Maszy_Member where Maszy_Member_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setLong(1, obj.getMember_Id());
			rs = pst.executeQuery();
			while (rs.next()) {
				obj = new Member();
				obj.setMember_Id(rs.getLong("Maszy_Member_Id"));
				obj.setMember_Name(rs.getString("Maszy_Member_Name"));

				obj.setMember_Remark(rs.getString("Maszy_Member_Remark"));
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}

	public List queryAll() {
		List list = new ArrayList();
		DBConnection con = new DBConnection();
		String sql = "select Maszy_Member.*,Maszy_Position.*,Maszy_DoorStore.* from Maszy_Member,Maszy_Position,Maszy_DoorStore where  Maszy_Member.Maszy_Position_Id=Maszy_Position.Maszy_Position_Id and  Maszy_Member.Maszy_DoorStore_Id=Maszy_DoorStore.Maszy_DoorStore_Id";
		try {
			pst = con.getConnection().prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
	public List<Member> queryByTerm(String where,String value){
		Member obj=new Member();
		List<Member> list = new ArrayList<Member>();
		DBConnection con = new DBConnection();
		String sql = "select * from Maszy_Member where "+where+" like ?";
		System.out.println( "select * from Maszy_Member where "+where+" like "+value);
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1, value);
			rs = pst.executeQuery();
			while (rs.next()) {
				obj = new Member();
				obj.setMember_Id(rs.getLong("Maszy_Member_Id"));
				obj.setMember_Name(rs.getString("Maszy_Member_Name"));
				obj.setMember_Phone(rs.getString("Maszy_Member_Phone"));
				obj.setMember_Adress(rs.getString("Maszy_Member_Adress"));
				obj.setMemberCard_Id(rs.getLong("Maszy_MemberCard_Id"));
				obj.setMember_Remark(rs.getString("Maszy_Member_Remark"));
				list.add(obj);
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
}
